package com.lgcns.android2;

import java.io.File;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {

    private static final String TAG = MainActivity.TAG;
    private static final int DATABASE_VERSION = 1;
    
    private static final String PROFILE_TABLE = "profile";
    private static final String TEAM_TABLE = "team";
    
    private static final String CREATE_PROFILE_TABLE_SQL = 
        "CREATE TABLE " + PROFILE_TABLE + " (" +
        "_id INTEGER," + 
        "id_num INTEGER," + 
        "title_code INTEGER," + 
        "title TEXT," + 
        "name TEXT," + 
        "tel TEXT," + 
        "email TEXT," + 
        "location TEXT," + 
        "job TEXT," + 
        "photo BLOB," + 
        "manager TEXT);"; 
    
    private static final String CREATE_TEAM_TABLE_SQL = 
        "CREATE TABLE "+ TEAM_TABLE + " (" +
        "_id INTEGER," + 
        "team_desc BLOB," + 
        "team_organization BLOB);";

    public DatabaseHelper(Context context) {
        super(context, Constants.DATABASE_FILE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_PROFILE_TABLE_SQL);
        db.execSQL(CREATE_TEAM_TABLE_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
                
        db.execSQL("DROP TABLE IF EXISTS " + PROFILE_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + TEAM_TABLE);
        
        onCreate(db);
    }

    /**
     * 업데이트 할 slite db 파일 객체를 받아 새로운 프로필 및 조직 데이터를 업데이트 
     * 
     * @param dbFile
     */
    public void updateDatabase(File dbFile) {
        SQLiteDatabase db = getWritableDatabase();
        
        db.execSQL("DROP TABLE IF EXISTS " + PROFILE_TABLE);
        db.execSQL("DROP TABLE IF EXISTS " + TEAM_TABLE);
        
        db.execSQL(CREATE_PROFILE_TABLE_SQL);
        db.execSQL(CREATE_TEAM_TABLE_SQL);
        
        // 다운로드한 파일을 SQLiteDatabase.openDatabase()를 통해 SQLiteDatabase 객체 얻음
        SQLiteDatabase newDb = SQLiteDatabase.openDatabase(dbFile.getAbsolutePath(), null, SQLiteDatabase.OPEN_READWRITE);
        
        // SQLiteDatabase.query를 통해 cursor 얻음
        Cursor cursor = newDb.query(PROFILE_TABLE, null, null, null, null, null, null);
        cursor.moveToFirst();

        do {
            ContentValues values = new ContentValues(10);
            
            values.put(Constants.ID_NUM, cursor.getInt(cursor.getColumnIndex(Constants.ID_NUM)));
            values.put(Constants.TITLE_CODE, cursor.getInt(cursor.getColumnIndex(Constants.TITLE_CODE)));
            values.put(Constants.TITLE, cursor.getString(cursor.getColumnIndex(Constants.TITLE)));
            values.put(Constants.NAME, cursor.getString(cursor.getColumnIndex(Constants.NAME)));
            values.put(Constants.TEL, cursor.getString(cursor.getColumnIndex(Constants.TEL)));
            values.put(Constants.EMAIL, cursor.getString(cursor.getColumnIndex(Constants.EMAIL)));
            values.put(Constants.LOCATION, cursor.getString(cursor.getColumnIndex(Constants.LOCATION)));
            values.put(Constants.JOB, cursor.getString(cursor.getColumnIndex(Constants.JOB)));
            values.put(Constants.PHOTO, cursor.getBlob(cursor.getColumnIndex(Constants.PHOTO)));
            values.put(Constants.MANAGER, cursor.getString(cursor.getColumnIndex(Constants.MANAGER)));
            
            db.insert(PROFILE_TABLE, null, values);
        } while(!cursor.isLast() && cursor.moveToNext());
        
        cursor.close();

        ///////////////////// team table ///////////////////////
        cursor = newDb.query(TEAM_TABLE, null, null, null, null, null, null);
        cursor.moveToFirst();

        ContentValues values = new ContentValues(2);
        
        values.put(Constants.TEAM_DESC, cursor.getBlob(cursor.getColumnIndex(Constants.TEAM_DESC)));
        values.put(Constants.TEAM_ORGANIZATION, cursor.getBlob(cursor.getColumnIndex(Constants.TEAM_ORGANIZATION)));

        db.insert(TEAM_TABLE, null, values);
        
        cursor.close();
        newDb.close();
        
        Log.d(TAG, "Finishing updating database");
    }
}
